using System;
using System.Data.SqlClient;
using System.IO;
using System.Data;
using System.Configuration;
using System.Web;
using System.Globalization;
using System.Web.Security;
using System.Web.UI;
using System.Net.Mail;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Collections;
using System.Collections.Generic;
using System.Text.RegularExpressions;
using IQKS.AWP.BusinessAccessLayer;
using System.Security.Cryptography;
using System.Text;
using System.Linq;
using Microsoft.ApplicationBlocks.Data;
using System.Web.Hosting;
using System.Xml;
using ICSharpCode.SharpZipLib.Zip;






/// <summary>
/// Summary description for CommonUtility
/// </summary>
public static class CommonUtility
{
    public static string ConvertDateyyyymmdd(string date)
    {
        string strfrmdate = string.Empty;
        if (date != "")
        {
            date = date.Replace(".", "/").Replace("-", "/");
            strfrmdate = date.Trim();
            string[] arrDate = strfrmdate.Split('/');
            string day = arrDate[0].ToString();
            string month = arrDate[1].ToString();
            string year = arrDate[2].ToString();
            strfrmdate = year + month + day;
        }
        return strfrmdate;
    }
    public static int ToInt(this string inputText)
    {
        int dataToReturn = 0;
        int.TryParse(inputText, out dataToReturn);
        return dataToReturn;
    }

    public static T ToChangeType<T>(this object inputText)
    {
        return (T)Convert.ChangeType(inputText, typeof(T));


    }

    public static string RemoveHTMLTags(this string Text)
    {
        return Regex.Replace(Text, @"<[^>]+>|&nbsp;", "").Trim();
    }

    public static void AddErrorToLog(this Exception ex,string Layer="Application")
    {
        new ErrorBAL().SetErrorMaster(ex.Message, ex.StackTrace, DateTime.Now, Layer);
    }

    public class RegExp
    {


        public static readonly string Date = "(0[1-9]|[12][0-9]|3[01])[-]" +
               "(0[1-9]|1[012])[-]((175[7-9])|(17[6-9][0-9])|(1[8-9][0-9][0-9])|" +
               "([2-9][0-9][0-9][0-9]))";
        // supports dates from 1-1-1757 to 31-12-9999 for SQL Server 2000 Date Range 
        public static readonly string Time = "(0[1-9]|[1][0-2])[:]" +
               "(0[0-9]|[1-5][0-9])[:](0[0-9]|[1-5][0-9])[ ][A|a|P|p][M|m]";
        public static readonly string Number = "[-+]?[0-9]*\\.?[0-9]*";
        public static readonly string Digit = "[0-9]*";
        public static readonly string NonNegative = "[+]?[0-9]*\\.?[0-9]*";

        public static string MaxLength(int len)
        {
            return "[\\s\\S]{0," + len.ToString() + "}";
        }
    }

    public class ValidationMessages
    {

        public static readonly string Url = "* Please enter a valid URL.<br>Valid " +
               "characters are all alphanumeric characters and .?" +
               "&_=-$<br> example: home.aspx?id=5&name=$my_name";
        public static readonly string Required = "* Required";
        public static readonly string Date =
           "* Please enter a valid date in dd-MM-yyyy format.";
        public static readonly string Time =
           "* Please enter a valid time in hh:mm:ss am format.";
        public static readonly string Number = "* Must be a valid number.";
        public static readonly string Digit = "* Must be a valid whole number.";
        public static readonly string NonNegative = "* Must be a non-negative number.";

        public static string MaxLength(int len)
        {
            return "* Maximum " + len.ToString() +
                   " characters are allowed.";
        }
    }


    public static bool CheckExtension(string strFile, string strFileTypeExt)
    {
        bool blExt = false;
        Char charSeprator = '|';
        string[] arrID;

        arrID = strFileTypeExt.Split(charSeprator);

        for (int i = 0; i <= strFileTypeExt.Split(charSeprator).GetUpperBound(0); i++)
        {
            if ("." + arrID[i].ToLower() == strFile.ToLower())
            { blExt = true; break; }
            else
            { blExt = false; }
        }
        return blExt;
    }
    public static DateTime CheckIsDate_and_FormatDate(string strDate)
    {
        DateTime dateValue = Convert.ToDateTime("01/01/1900");
        bool flag = false;
        bool isdate = false;

        try
        {
            dateValue = DateTime.Parse(strDate);
            flag = true;
            isdate = true;
        }
        catch (FormatException)
        {

        }

        if (flag == false)
        {
            try
            {
                dateValue = DateTime.Parse(strDate, new CultureInfo("fr-FR", false));
                isdate = true;
            }
            catch (FormatException)
            {
                isdate = false;
            }
        }

        if (isdate == false)
        {
            dateValue = Convert.ToDateTime("01/01/1900");
        }

        return dateValue;
    }

    public static void AddSelectToDropDown(ref DropDownList dropDownList)
    {
        dropDownList.Items.Insert(0, "--- Select ---");
    }

    public static void AddSelectToDropDownAsEmptyValue(ref DropDownList dropDownList)
    {
        dropDownList.Items.Insert(0, new ListItem("--- Select ---", "0"));
    }

    public static void AddSelectAllToDropDownAsEmptyValue(ref DropDownList dropDownList)
    {
        dropDownList.Items.Insert(0, new ListItem("--- Select All ---", "0"));
    }


    public static void AddSelectToDDLWithOnlySelect(ref DropDownList dropDownList)
    {
        dropDownList.Items.Insert(0, new ListItem("-Select-"));
    }
    public static void AddOtherToDropDown(ref DropDownList dropDownList)
    {
        dropDownList.Items.Add("Other");
    }

    public static void AddNoValueToDropDown(ref DropDownList dropDownList)
    {
        int items_count = 0;
        items_count = dropDownList.Items.Count;
        dropDownList.Items.Insert(items_count, new ListItem("No Data", "-"));
    }

    public static void AddSelectToDropDownWithBlank(ref DropDownList dropDownList)
    {
        dropDownList.Items.Insert(0, new ListItem("--- Select ---", ""));
    }


    public static void AddSelectToDropDownWithMinues(ref DropDownList dropDownList)
    {
        dropDownList.Items.Insert(0, new ListItem("--- Select ---", "-1"));
    }


    public static string ConvertDatatableToXML(DataTable dt)
    {
        MemoryStream str = new MemoryStream();
        dt.WriteXml(str, true);
        str.Seek(0, SeekOrigin.Begin);
        StreamReader sr = new StreamReader(str);
        string xmlstr;
        xmlstr = sr.ReadToEnd();
        return (xmlstr);
    }

    public static void Set2ndValDefaultForDropDown(ref DropDownList dropDownList)
    {
        if (dropDownList.Items.Count > 1)
            dropDownList.SelectedIndex = 1;
    }
    //public static void BindDropDownKitType(ref DropDownList dropDownList)
    //{
    //    string[] ddlVal = GeneralInsuranceConfiguration.Current.KitType.Value.Split(',');
    //    for (int i = 0; i <= ddlVal.Length - 1; i++)
    //    {
    //        string[] ddlValues = ddlVal[i].Split('$');
    //        dropDownList.Items.Add(new ListItem(ddlValues[1], ddlValues[0]));
    //    }
    //}
    public static void SetDefaultForDropDown(ref DropDownList dropDownList)
    {
        if (dropDownList.Items.Count > 0)
            dropDownList.SelectedIndex = 0;
    }
    public static void ClearDropDown(ref DropDownList dropDownList)
    {
        dropDownList.Items.Clear();
    }
    public static void BindDropDown(ref DropDownList dropDownList, object source, string textField, string valueField)
    {
        dropDownList.DataSource = source;
        dropDownList.DataTextField = textField;
        dropDownList.DataValueField = valueField;
        dropDownList.DataBind();
    }

    public static void BindCheckBoxList(ref CheckBoxList chkDownList, object source, string textField, string valueField)
    {
        chkDownList.DataSource = source;
        chkDownList.DataTextField = textField;
        chkDownList.DataValueField = valueField;
        chkDownList.DataBind();
    }

    public static void BindListbox(ref ListBox listbox, object source, string textField, string valueField)
    {
        listbox.DataSource = source;
        listbox.DataTextField = textField;
        listbox.DataValueField = valueField;
        listbox.DataBind();
    }

    public static void BindRadioButtonList(ref RadioButtonList rbDownList, object source, string textField, string valueField)
    {
        rbDownList.DataSource = source;
        rbDownList.DataTextField = textField;
        rbDownList.DataValueField = valueField;
        rbDownList.DataBind();
    }
    //public static void ClearMessage(ref Label label)
    //{
    //    label.Text = "";
    //}
    //public static void ShowMessage(ref Label label)
    //{
    //    label.Text = InsuranceResources.GetMessage(Message.UpdateMessage); 
    //}
    //public static string GetBranchSrManagerRoleName()
    //{
    //    return GeneralInsuranceConfiguration.Current.BranchSeniorManagerRoleName.Value;
    //}

    public static Guid GetLoggedOnUserId()
    {

        return new Guid(Membership.GetUser().ProviderUserKey.ToString());

    }
    public static string LastLoginDate()
    {


        return Membership.GetUser().LastLoginDate.ToString();
    }
    public static string GetLoggedOnUserName()
    {
        return Membership.GetUser().UserName.ToString();


    }

    public static string GetCurrentUserRole()
    {
        try
        {
            return Roles.GetRolesForUser()[0].ToString();
        }
        catch
        {
            return "";
        }
    }

    //public static string GetTelesalesAdminRoleName()
    //{
    //    return GeneralInsuranceConfiguration.Current.TelesalesAdminRoleName.Value;
    //}
    //public static string GetTelesalesCallCenterRoleName()
    //{
    //    return GeneralInsuranceConfiguration.Current.TelesalesCallCenterRoleName.Value;
    //}

    //public static string GetRpcOperationRoleName()
    //{
    //    return GeneralInsuranceConfiguration.Current.RpcOperationRoleName.Value;
    //}
    //public static string GetRpcDeoRoleName()
    //{
    //    return GeneralInsuranceConfiguration.Current.RpcDeoRoleName.Value;
    //}




    //public static string GetSubAgentRoleName()
    //{
    //    return GeneralInsuranceConfiguration.Current.SubAgentRoleName.Value;
    //}
    //public static string GetIntermediaryRoleName()
    //{
    //    return GeneralInsuranceConfiguration.Current.IntermediaryRoleName.Value;
    //}
    //public static string GetSalesManagerRoleName()
    //{
    //    return GeneralInsuranceConfiguration.Current.SalesManagerRoleName.Value;
    //}
    //public static string GetBranchManagerRoleName()
    //{

    //    return GeneralInsuranceConfiguration.Current.BranchManagerRoleName.Value;  
    //}
    //public static string GetCallCenterRoleName()
    //{
    //    return GeneralInsuranceConfiguration.Current.CallCenterRoleName.Value;
    //}
    public static void SetLabelHeading(Page page, string heading)
    {
        //Label lblHeading = (Label)page.Master.FindControl("lblHeading");
        //lblHeading.Text = heading;

    }
    public static bool SendMail(string mailFrom, string mailTo, string mailBody, string mailSubject)
    {
        try
        {
            MailAddress fromMailAddress = new MailAddress(mailFrom);
            //MailAddress toMailAddress = new MailAddress(mailTo);
            MailMessage mailMessage = new MailMessage();///fromMailAddress, toMailAddress);
            mailMessage.Body = mailBody;
            mailMessage.From = fromMailAddress;
            mailMessage.Subject = mailSubject;
            mailMessage.To.Add(mailTo.ToString());
            SmtpClient smtpClient = new SmtpClient();
            smtpClient.Send(mailMessage);
            return true;
        }
        catch (Exception ex)
        {
             throw new ApplicationException(ex.Message);
        }
    }

    public static bool SendMailWithCC(string mailFrom, string mailTo, string mailCC, string mailBody, string mailSubject)
    {
        try
        {
            MailAddress fromMailAddress = new MailAddress(mailFrom);
            //MailAddress toMailAddress = new MailAddress(mailTo);
            //MailAddress ccAddress = new MailAddress(mailCC);
            MailMessage mailMessage = new MailMessage();//(fromMailAddress, toMailAddress);
            mailMessage.Body = mailBody;
            mailMessage.Subject = mailSubject;
            mailMessage.From = fromMailAddress;
            mailMessage.To.Add(mailTo.ToString());
            mailMessage.CC.Add(mailCC.ToString());
            SmtpClient smtpClient = new SmtpClient();
            smtpClient.Send(mailMessage);
            return true;
        }
        catch (Exception ex)
        {
            throw new ApplicationException(ex.Message);
        }
    }

    public static void FillddlKitRange(ref DropDownList ddlKitRange)
    {
        try
        {
            for (int i = 1; i <= 50; i++)
            {
                ddlKitRange.Items.Add(i.ToString());
            }
            ddlKitRange.Items.Add("60");
            ddlKitRange.Items.Add("70");
            ddlKitRange.Items.Add("75");
            ddlKitRange.Items.Add("80");
            ddlKitRange.Items.Add("90");
            ddlKitRange.Items.Add("100");
            ddlKitRange.Items.Add("200");
            ddlKitRange.Items.Add("300");
            ddlKitRange.Items.Add("400");
            ddlKitRange.Items.Add("500");

            /*
            ddlKitRange.Items.Add("200");
            ddlKitRange.Items.Add("300");
            ddlKitRange.Items.Add("600");
            ddlKitRange.Items.Add("700");
            ddlKitRange.Items.Add("800");
            ddlKitRange.Items.Add("900");
            ddlKitRange.Items.Add("1000");
            */
        }
        catch (Exception ex)
        {
            throw new ApplicationException(ex.Message);
        }
    }

    ///Purpose   : Convert String To DateTime 
    ///Parameter : String dateTime
    ///Return    : DateTime objDate
    public static DateTime ConvertStringToMMddyyyy(String strDateTime)
    {
        DateTimeFormatInfo dtfi = new DateTimeFormatInfo();
        dtfi.ShortDatePattern = "dd/MM/yyyy";
        dtfi.DateSeparator = "/";
        DateTime objDate = Convert.ToDateTime(strDateTime, dtfi);
        return objDate;
    }

    /////Purpose   : Convert DateTime To String 
    /////Parameter : String dateTime
    /////Return    : DateTime objDate
    public static String ConvertDateTimeToddMMyyyy(DateTime dateTime)
    {
        return String.Format("{0:dd/MM/yyyy}", dateTime).ToString();
    }

    ///Purpose   : Convert String To DateTime "MM/dd/yyyy"
    ///Parameter : String dateTime
    ///Return    : String
    public static String ConvertDateToMMddyyyy(String strDateTime)
    {
        DateTimeFormatInfo dtfi = new DateTimeFormatInfo();
        dtfi.ShortDatePattern = "dd/MM/yyyy";
        dtfi.DateSeparator = "/";
        DateTime objDate = Convert.ToDateTime(strDateTime, dtfi);
        return String.Format("{0:MM/dd/yyyy}", objDate).ToString();
    }

    public static String ConvertDateToddMMYYY_hhmmss(String strDateTime)
    {
        if (!string.IsNullOrEmpty(strDateTime))
        {
            DateTime dt = Convert.ToDateTime(strDateTime);

            return dt.ToString("dd/MM/yyyy hh:mm:ss");
        }
        else
        {
            return "";
        }
    }

    public static DateTime ConvertStringToMMddyyyy_hhmmss(String strDateTime)
    {
        DateTimeFormatInfo dtfi = new DateTimeFormatInfo();
        dtfi.ShortDatePattern = "dd/MM/yyyy hh:mm:ss";
        dtfi.DateSeparator = "/";
        DateTime objDate = Convert.ToDateTime(strDateTime, dtfi);
        return objDate;
    }

    #region Maintain Checkbox State
    public static void RememberOldCheckBoxValues(ref GridView gvProductKits, Page page, string sessionName)
    {
        try
        {
            Hashtable hashtable = new Hashtable();
            string index = "";
            string storeChkValues = "";
            foreach (GridViewRow row in gvProductKits.Rows)
            {
                index = gvProductKits.DataKeys[row.RowIndex].Value.ToString();
                storeChkValues = "";
                for (int controlCount = 0; controlCount < row.Controls.Count; controlCount++)
                {
                    if (row.Controls[controlCount].GetType() == typeof(DataControlFieldCell))
                    {
                        for (int count = 0; count < row.Controls[controlCount].Controls.Count; count++)
                        {
                            Control control = row.Controls[controlCount].Controls[count];
                            if (control is CheckBox)
                            {
                                CheckBox currentCheckBox = (CheckBox)control.FindControl(control.ID);
                                storeChkValues += currentCheckBox.Checked.ToString() + ",";
                            }
                        }
                    }
                }
                storeChkValues = storeChkValues.TrimEnd(',');

                // Check in the Session 
                if (page.Session[sessionName] != null)
                {
                    hashtable = (Hashtable)page.Session[sessionName];
                }
                if (hashtable.Contains(index))
                {
                    hashtable.Remove(index);
                }
                hashtable.Add(index, storeChkValues);
            }

            if (hashtable != null && hashtable.Count > 0)
                page.Session[sessionName] = hashtable;
        }
        catch (Exception ex)
        {
            throw new ApplicationException(ex.Message);
        }
    }

    public static void RePopulateCheckBoxValues(ref GridView gvProductKits, Page page, string sessionName)
    {
        try
        {

            Hashtable hashtable = (Hashtable)page.Session[sessionName];
            if (hashtable != null && hashtable.Count > 0)
            {
                foreach (GridViewRow row in gvProductKits.Rows)
                {
                    string index = gvProductKits.DataKeys[row.RowIndex].Value.ToString();

                    if (hashtable.Contains(index))
                    {
                        string[] checkBoxValues = hashtable[index].ToString().Split(',');

                        int checkBoxValuesIndex = 0;
                        for (int controlCount = 0; controlCount < row.Controls.Count; controlCount++)
                        {
                            if (row.Controls[controlCount].GetType() == typeof(DataControlFieldCell))
                            {
                                for (int count = 0; count < row.Controls[controlCount].Controls.Count; count++)
                                {
                                    Control control = row.Controls[controlCount].Controls[count];
                                    if (control is CheckBox)
                                    {
                                        CheckBox currentCheckBox = (CheckBox)control.FindControl(control.ID);
                                        currentCheckBox.Checked = Convert.ToBoolean(checkBoxValues[checkBoxValuesIndex]);
                                        checkBoxValuesIndex++;
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
        catch (Exception ex)
        {
            throw new ApplicationException(ex.Message);
        }
    }
    #endregion
    //public static string GetCurentProductID(Page p)
    //{
    //    return p.Session[GeneralInsuranceConfiguration.Current.SessionProductId.Value].ToString();

    //}

    public static string GetUserHostAddress(Page p)
    {
        return p.Request.UserHostAddress;

    }
    public static DateTime GetLoginTime()
    {
        return System.DateTime.Now;
    }

    //public static void SetLoginTracking(string ClientIP, string UserName, string UserID, string RoleName,string hostName, string hostAddress)
    //{
    //    new ProductKitBAL().SetLoginTracking(ClientIP, UserName, UserID, RoleName,hostName,hostAddress);
    //}

    public static void ExportToExcel(DataSet source, string fileName)
    {

        System.IO.StreamWriter excelDoc;
        excelDoc = new System.IO.StreamWriter(fileName);
        const string startExcelXML = "<xml version>\r\n<Workbook " +
              "xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" +
              " xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " +
              "xmlns:x=\"urn:schemas-    microsoft-com:office:" +
              "excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:" +
              "office:spreadsheet\">\r\n <Styles>\r\n " +
              "<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " +
              "<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>" +
              "\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>" +
              "\r\n <Protection/>\r\n </Style>\r\n " +
              "<Style ss:ID=\"BoldColumn\">\r\n <Font " +
              "x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n " +
              "<Style     ss:ID=\"StringLiteral\">\r\n <NumberFormat" +
              " ss:Format=\"@\"/>\r\n </Style>\r\n <Style " +
              "ss:ID=\"Decimal\">\r\n <NumberFormat " +
              "ss:Format=\"0.0000\"/>\r\n </Style>\r\n " +
              "<Style ss:ID=\"Integer\">\r\n <NumberFormat " +
              "ss:Format=\"0\"/>\r\n </Style>\r\n <Style " +
              "ss:ID=\"DateLiteral\">\r\n <NumberFormat " +
              "ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n " +
              "</Styles>\r\n ";
        const string endExcelXML = "</Workbook>";

        int rowCount = 0;
        int sheetCount = 1;
        excelDoc.Write(startExcelXML);
        for (int i = 0; i < source.Tables.Count; i++)
        {
            if (sheetCount > 1)
            {
                sheetCount = sheetCount + 1;
            }
            excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
            excelDoc.Write("<Table>");
            excelDoc.Write("<Row>");
            for (int x = 0; x < source.Tables[i].Columns.Count; x++)
            {
                excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
                excelDoc.Write(source.Tables[i].Columns[x].ColumnName);
                excelDoc.Write("</Data></Cell>");
            }
            excelDoc.Write("</Row>");
            foreach (DataRow x in source.Tables[i].Rows)
            {
                rowCount++;
                //if the number of rows is > 64000 create a new page to continue output
                if (rowCount == 64000)
                {
                    rowCount = 0;
                    sheetCount++;
                    excelDoc.Write("</Table>");
                    excelDoc.Write(" </Worksheet>");
                    excelDoc.Write("<Worksheet ss:Name=\"Sheet" + sheetCount + "\">");
                    excelDoc.Write("<Table>");

                    excelDoc.Write("<Row>");
                    for (int j = 0; j < source.Tables[i].Columns.Count; j++)
                    {
                        excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
                        excelDoc.Write(source.Tables[i].Columns[j].ColumnName);
                        excelDoc.Write("</Data></Cell>");
                    }
                    excelDoc.Write("</Row>");

                }
                excelDoc.Write("<Row>"); //ID=" + rowCount + "
                for (int y = 0; y < source.Tables[i].Columns.Count; y++)
                {
                    System.Type rowType;
                    rowType = x[y].GetType();
                    switch (rowType.ToString())
                    {
                        case "System.String":
                            string XMLstring = x[y].ToString();
                            XMLstring = XMLstring.Trim();
                            XMLstring = XMLstring.Replace("&", "&");
                            XMLstring = XMLstring.Replace(">", ">");
                            XMLstring = XMLstring.Replace("<", "<");
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                           "<Data ss:Type=\"String\">");
                            excelDoc.Write(XMLstring);
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.DateTime":
                            //Excel has a specific Date Format of YYYY-MM-DD followed by  
                            //the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000
                            //The Following Code puts the date stored in XMLDate 
                            //to the format above
                            DateTime XMLDate = (DateTime)x[y];
                            string XMLDatetoString = ""; //Excel Converted Date
                            XMLDatetoString = XMLDate.Year.ToString() +
                                 "-" +
                                 (XMLDate.Month < 10 ? "0" +
                                 XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
                                 "-" +
                                 (XMLDate.Day < 10 ? "0" +
                                 XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
                                 "T" +
                                 (XMLDate.Hour < 10 ? "0" +
                                 XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
                                 ":" +
                                 (XMLDate.Minute < 10 ? "0" +
                                 XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
                                 ":" +
                                 (XMLDate.Second < 10 ? "0" +
                                 XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
                                 ".000";
                            excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" +
                                         "<Data ss:Type=\"DateTime\">");
                            excelDoc.Write(XMLDatetoString);
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Boolean":
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                        "<Data ss:Type=\"String\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
                                    "<Data ss:Type=\"Number\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Decimal":
                        case "System.Double":
                            excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +
                                  "<Data ss:Type=\"Number\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.DBNull":
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                  "<Data ss:Type=\"String\">");
                            excelDoc.Write("");
                            excelDoc.Write("</Data></Cell>");
                            break;
                        default:
                            throw (new Exception(rowType.ToString() + " not handled."));
                    }
                }
                excelDoc.Write("</Row>");
            }
            excelDoc.Write("</Table>");
            excelDoc.Write(" </Worksheet>");
        }
        excelDoc.Write(endExcelXML);
        excelDoc.Close();
    }



    //the below code add by Chinnayya kasina 
    public static void DownLoadFileDataInExcel(ref DataSet ds, String ServerFileFolderPath)
    {


        ExportToExcel(ds, ServerFileFolderPath);
        ds = null;
        HttpContext.Current.Response.ContentType = "application/octet-stream";
        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.IO.Path.GetFileName(ServerFileFolderPath));
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.WriteFile(ServerFileFolderPath);
        HttpContext.Current.Response.End();

    }

    //public static string GetCurrentUserEmailID(string UserName, string RoleName)
    //{
    //    return new ProductKitBAL().GetCurrentUserEmailID(UserName, RoleName).ToString();
    //}
    //public static string GetCurrentZonalEmailID(string UserName, string RoleName)
    //{
    //    return new ProductKitBAL().GetCurrentZonalEmailID(UserName, RoleName).ToString();
    //}
    //public static string GetCurrentROMEmailID(string UserName, string RoleName)
    //{
    //    return new ProductKitBAL().GetCurrentROMEmailID(UserName, RoleName).ToString();
    //}



    ///// <summary>
    ///// Get the EMail of Current User as per RoleName
    ///// </summary>
    ///// <param name="UserName">UserName</param>
    ///// <param name="RoleName">RoleName</param>
    ///// <param name="ProductID">ProductID</param>
    ///// <returns>EMail ID of Current User as per Role</returns>
    //public static string GetEmailIDAsPerRole(string UserName, string RoleName,string ProductID)
    //{
    //    //return new ProductKitBAL().GetEmailIDAsPerRole(UserName, RoleName, ProductID).ToString();
    //    return Convert.ToString(RelianceGeneralInsurance.GeneralInsuranceDAL.DataUtils.ExecuteScalar("usp_GetEmailIDAsPerRole", UserName,RoleName,ProductID));//Added on 01-09-08 to For making mail functionality working
    //}

    ///// <summary>
    ///// Get the Static Values of the Static Variables
    ///// </summary>
    ///// <param name="strStaticVariable">Name of Static Variable</param>
    ///// <returns>Value of Static Variable</returns>
    //public static string GetStaticValues(string strStaticVariable)
    //{
    //    return Convert.ToString(RelianceGeneralInsurance.GeneralInsuranceDAL.DataUtils.ExecuteScalar("usp_GetStaticValues", strStaticVariable));
    //}

    //public static Guid? GetBranchByDEO(string DeoCode)
    //{
    //   object obj=(DataUtils.ExecuteScalar("usp_GetBranchByDEO", DeoCode));
    //   return (new Guid(obj.ToString()));
    //}

    //public static DataSet GetStaticValuesToEdit(string UserName,string RoleName)
    //{
    //    return DataUtils.ExecuteDataset("usp_GetStaticValuesToEdit", UserName, RoleName);
    //}

    //public static void SetStaticValuesToEdit(string StaticValues, string StaticVar, string UserName, string RoleName)
    //{
    //    DataUtils.ExecuteNonQuery ("usp_SetStaticValuesToEdit", StaticValues, StaticVar,UserName,RoleName);
    //}


    //public static void WriteError(string errorMessage)
    //{
    //    try
    //    {
    //        string path = "~/Error/" + DateTime.Today.ToString("dd-MMM-yy") + ".txt";
    //        if (!File.Exists(System.Web.HttpContext.Current.Server.MapPath(path)))
    //        {
    //            File.Create(System.Web.HttpContext.Current.Server.MapPath(path)).Close();
    //        }
    //        using (StreamWriter w = File.AppendText(System.Web.HttpContext.Current.Server.MapPath(path)))
    //        {
    //            w.WriteLine("\r\nLog Entry : ");
    //            w.WriteLine("{0}", DateTime.Now.ToString(CultureInfo.InvariantCulture));
    //            string err = "Error in: " + System.Web.HttpContext.Current.Request.Url.ToString() +
    //                          ". Error Message:" + errorMessage;
    //            w.WriteLine(err);
    //            w.WriteLine("__________________________");
    //            w.Flush();
    //            w.Close();
    //        }
    //    }
    //    catch (Exception ex)
    //    {
    //        WriteError(ex.Message);
    //    }

    //}





    //public static DataSet Get_Roles()
    //{
    //    try
    //    {
    //        return DataUtils.ExecuteDataset("USP_Get_Roles");
    //    }
    //    catch (Exception ex)
    //    {
    //        return null;
    //    }
    //}

    public static int GetHour(int hours)
    {
        if (hours == 12)
        {
            hours = 0;
        }
        else if (hours > 12)
        {
            hours -= 12;
        }
        return hours;
    }

    public static string Encrypt(string toEncrypt, bool useHashing)
    {
        byte[] keyArray;
        byte[] toEncryptArray = UTF8Encoding.UTF8.GetBytes(toEncrypt);

        System.Configuration.AppSettingsReader settingsReader =
                                            new AppSettingsReader();
        // Get the key from config file

        string key = (string)settingsReader.GetValue("SecurityKey",
                                                         typeof(String));
        //System.Windows.Forms.MessageBox.Show(key);
        //If hashing use get hashcode regards to your key
        if (useHashing)
        {
            MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();
            keyArray = hashmd5.ComputeHash(UTF8Encoding.UTF8.GetBytes(key));
            //Always release the resources and flush data
            // of the Cryptographic service provide. Best Practice

            hashmd5.Clear();
        }
        else
            keyArray = UTF8Encoding.UTF8.GetBytes(key);

        TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();
        //set the secret key for the tripleDES algorithm
        tdes.Key = keyArray;
        //mode of operation. there are other 4 modes.
        //We choose ECB(Electronic code Book)
        tdes.Mode = CipherMode.ECB;
        //padding mode(if any extra byte added)

        tdes.Padding = PaddingMode.PKCS7;

        ICryptoTransform cTransform = tdes.CreateEncryptor();
        //transform the specified region of bytes array to resultArray
        byte[] resultArray =
          cTransform.TransformFinalBlock(toEncryptArray, 0,
          toEncryptArray.Length);
        //Release resources held by TripleDes Encryptor
        tdes.Clear();
        //Return the encrypted data into unreadable string format
        return Convert.ToBase64String(resultArray, 0, resultArray.Length);
    }

    public static string Decrypt(string cipherString, bool useHashing)
    {
        byte[] keyArray;
        //replacewhite space
        cipherString = cipherString.Replace(" ", "+");
        //get the byte code of the string
        
        byte[] toEncryptArray = Convert.FromBase64String(cipherString);

        System.Configuration.AppSettingsReader settingsReader =
                                            new AppSettingsReader();
        //Get your key from config file to open the lock!
        string key = (string)settingsReader.GetValue("SecurityKey",
                                                     typeof(String));

        if (useHashing)
        {
            //if hashing was used get the hash code with regards to your key
            MD5CryptoServiceProvider hashmd5 = new MD5CryptoServiceProvider();
            keyArray = hashmd5.ComputeHash(UTF8Encoding.UTF8.GetBytes(key));
            //release any resource held by the MD5CryptoServiceProvider

            hashmd5.Clear();
        }
        else
        {
            //if hashing was not implemented get the byte code of the key
            keyArray = UTF8Encoding.UTF8.GetBytes(key);
        }

        TripleDESCryptoServiceProvider tdes = new TripleDESCryptoServiceProvider();
        //set the secret key for the tripleDES algorithm
        tdes.Key = keyArray;
        //mode of operation. there are other 4 modes. 
        //We choose ECB(Electronic code Book)

        tdes.Mode = CipherMode.ECB;
        //padding mode(if any extra byte added)
        tdes.Padding = PaddingMode.PKCS7;

        ICryptoTransform cTransform = tdes.CreateDecryptor();
        byte[] resultArray = cTransform.TransformFinalBlock(
                             toEncryptArray, 0, toEncryptArray.Length);
        //Release resources held by TripleDes Encryptor                
        tdes.Clear();
        //return the Clear decrypted TEXT
        return UTF8Encoding.UTF8.GetString(resultArray);
    }
    public static int ErrorLog_Report(string ReportName, Guid LoginId, string LoginName, string ExcelFileName, string Parameter)
    {
        try
        {
            return CommonUtility.ExecuteNonQuery("USP_ERRORLOG_REPORT_INSERT", ReportName, LoginId, LoginName, ExcelFileName, Parameter);
        }
        catch (Exception ex)
        {
            throw new ApplicationException(ex.Message);
        }
    }

    private static string connectionString= ConfigurationManager.ConnectionStrings["ConnLeadCreation"].ConnectionString;
    public static int ExecuteNonQuery(string storedProcedureName, params object[] parameters)
    {
        //return SqlHelper.ExecuteNonQuery(connectionString, storedProcedureName,
        //    CommandType.StoredProcedure, parameters);
        return SqlHelper.ExecuteNonQuery(connectionString, storedProcedureName, parameters);
    }
    public static void ExportToExcel_Sheet(DataSet source, string fileName)
    {
        string ServerFileFolderPath = fileName;
        System.IO.StreamWriter excelDoc;
        excelDoc = new System.IO.StreamWriter(fileName);
        const string startExcelXML = "<xml version>\r\n<Workbook " +
              "xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"\r\n" +
              " xmlns:o=\"urn:schemas-microsoft-com:office:office\"\r\n " +
              "xmlns:x=\"urn:schemas-    microsoft-com:office:" +
              "excel\"\r\n xmlns:ss=\"urn:schemas-microsoft-com:" +
              "office:spreadsheet\">\r\n <Styles>\r\n " +
              "<Style ss:ID=\"Default\" ss:Name=\"Normal\">\r\n " +
              "<Alignment ss:Vertical=\"Bottom\"/>\r\n <Borders/>" +
              "\r\n <Font/>\r\n <Interior/>\r\n <NumberFormat/>" +
              "\r\n <Protection/>\r\n </Style>\r\n " +
              "<Style ss:ID=\"BoldColumn\">\r\n <Font " +
              "x:Family=\"Swiss\" ss:Bold=\"1\"/>\r\n </Style>\r\n " +
              "<Style     ss:ID=\"StringLiteral\">\r\n <NumberFormat" +
              " ss:Format=\"@\"/>\r\n </Style>\r\n <Style " +
              "ss:ID=\"Decimal\">\r\n <NumberFormat " +
              "ss:Format=\"0.0000\"/>\r\n </Style>\r\n " +
              "<Style ss:ID=\"Integer\">\r\n <NumberFormat " +
              "ss:Format=\"0\"/>\r\n </Style>\r\n <Style " +
              "ss:ID=\"DateLiteral\">\r\n <NumberFormat " +
              "ss:Format=\"mm/dd/yyyy;@\"/>\r\n </Style>\r\n " +
              "</Styles>\r\n ";
        const string endExcelXML = "</Workbook>";

        int rowCount = 0;
        int sheetCount = 1;
        excelDoc.Write(startExcelXML);
        for (int i = 0; i < source.Tables.Count; i++)
        {
            string SheetName = null;
            if (i == 0)
            {
                SheetName = "Sheet1";
            }
            else
            {
                SheetName = "Sheet2";
            }

            //if (sheetCount > 1)
            //{
            //sheetCount = sheetCount + 1;
            //}
            excelDoc.Write("<Worksheet ss:Name=\"" + SheetName + "\">");
            excelDoc.Write("<Table>");
            excelDoc.Write("<Row>");
            for (int x = 0; x < source.Tables[i].Columns.Count; x++)
            {
                excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
                excelDoc.Write(source.Tables[i].Columns[x].ColumnName);
                excelDoc.Write("</Data></Cell>");
            }
            excelDoc.Write("</Row>");
            foreach (DataRow x in source.Tables[i].Rows)
            {
                rowCount++;
                //if the number of rows is > 64000 create a new page to continue output
                if (rowCount == 64000)
                {
                    rowCount = 0;
                    sheetCount++;
                    excelDoc.Write("</Table>");
                    excelDoc.Write(" </Worksheet>");
                    excelDoc.Write("<Worksheet ss:Name=\"" + SheetName + "\">");
                    excelDoc.Write("<Table>");

                    excelDoc.Write("<Row>");
                    for (int j = 0; j < source.Tables[i].Columns.Count; j++)
                    {
                        excelDoc.Write("<Cell ss:StyleID=\"BoldColumn\"><Data ss:Type=\"String\">");
                        excelDoc.Write(source.Tables[i].Columns[j].ColumnName);
                        excelDoc.Write("</Data></Cell>");
                    }
                    excelDoc.Write("</Row>");
                }
                excelDoc.Write("<Row>"); //ID=" + rowCount + "
                for (int y = 0; y < source.Tables[i].Columns.Count; y++)
                {
                    System.Type rowType;
                    rowType = x[y].GetType();
                    switch (rowType.ToString())
                    {
                        case "System.String":
                            string XMLstring = x[y].ToString();
                            XMLstring = XMLstring.Trim();
                            XMLstring = XMLstring.Replace("&", "&");
                            XMLstring = XMLstring.Replace(">", ">");
                            XMLstring = XMLstring.Replace("<", "<");
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                           "<Data ss:Type=\"String\">");
                            excelDoc.Write(XMLstring);
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.DateTime":
                            //Excel has a specific Date Format of YYYY-MM-DD followed by  
                            //the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000
                            //The Following Code puts the date stored in XMLDate 
                            //to the format above
                            DateTime XMLDate = (DateTime)x[y];
                            string XMLDatetoString = ""; //Excel Converted Date
                            XMLDatetoString = XMLDate.Year.ToString() +
                                 "-" +
                                 (XMLDate.Month < 10 ? "0" +
                                 XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
                                 "-" +
                                 (XMLDate.Day < 10 ? "0" +
                                 XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
                                 "T" +
                                 (XMLDate.Hour < 10 ? "0" +
                                 XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
                                 ":" +
                                 (XMLDate.Minute < 10 ? "0" +
                                 XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
                                 ":" +
                                 (XMLDate.Second < 10 ? "0" +
                                 XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
                                 ".000";
                            excelDoc.Write("<Cell ss:StyleID=\"DateLiteral\">" +
                                         "<Data ss:Type=\"DateTime\">");
                            excelDoc.Write(XMLDatetoString);
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Boolean":
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                        "<Data ss:Type=\"String\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Int16":
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            excelDoc.Write("<Cell ss:StyleID=\"Integer\">" +
                                    "<Data ss:Type=\"Number\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.Decimal":
                        case "System.Double":
                            excelDoc.Write("<Cell ss:StyleID=\"Decimal\">" +
                                  "<Data ss:Type=\"Number\">");
                            excelDoc.Write(x[y].ToString());
                            excelDoc.Write("</Data></Cell>");
                            break;
                        case "System.DBNull":
                            excelDoc.Write("<Cell ss:StyleID=\"StringLiteral\">" +
                                  "<Data ss:Type=\"String\">");
                            excelDoc.Write("");
                            excelDoc.Write("</Data></Cell>");
                            break;
                        default:
                            throw (new Exception(rowType.ToString() + " not handled."));
                    }
                }
                excelDoc.Write("</Row>");
            }
            excelDoc.Write("</Table>");
            excelDoc.Write(" </Worksheet>");
        }
        excelDoc.Write(endExcelXML);
        excelDoc.Close();



        FileInfo info = new FileInfo(ServerFileFolderPath);
        long len = info.Length;
        if (len > 30101297)
        {
            using (ZipOutputStream s = new ZipOutputStream(File.Create(ServerFileFolderPath.Replace("xls", "zip"))))
            {
                s.SetLevel(9);

                byte[] buffer = new byte[4096];
                ZipEntry entry = new ZipEntry(Path.GetFileName(ServerFileFolderPath));
                entry.DateTime = DateTime.Now;
                s.PutNextEntry(entry);
                using (FileStream fs = File.OpenRead(ServerFileFolderPath))
                {
                    int sourceBytes;
                    do
                    {
                        sourceBytes = fs.Read(buffer, 0, buffer.Length);
                        s.Write(buffer, 0, sourceBytes);

                    } while (sourceBytes > 0);
                }

                s.Finish();
                //s.Close();
            }

            HttpContext.Current.Response.ContentType = "application/octet-stream";
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.IO.Path.GetFileName(ServerFileFolderPath.Replace("xls", "zip")));
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.WriteFile(ServerFileFolderPath.Replace("xls", "zip"));
            HttpContext.Current.Response.End();
        }
        else
        {
            HttpContext.Current.Response.ContentType = "application/octet-stream";
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.IO.Path.GetFileName(ServerFileFolderPath));
            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.WriteFile(ServerFileFolderPath);
            HttpContext.Current.Response.End();
        }
    }

    public static void SaveFile(FileUpload flUpload, string pStrFolderPath, string pStrFileName)
    {
        if (Directory.Exists(pStrFolderPath) == false)
            Directory.CreateDirectory(pStrFolderPath);
        flUpload.SaveAs(pStrFolderPath + pStrFileName);
    }
    public static void WriteErrorLog(string LogPath,string Message)
    {
        StreamWriter sw = null;
        try
        {
            
            sw = new StreamWriter( LogPath , true);
            sw.WriteLine(DateTime.Now.ToString() + ": " + Message);
            sw.Flush();
            sw.Close();
        }
        catch
        {
        }
    }
}
